/*
 * Copyright 2015 泛泛o0之辈
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package cn.jfast.framework.jdbc.orm.sql;

import cn.jfast.framework.base.util.*;
import cn.jfast.framework.jdbc.info.Column;
import cn.jfast.framework.jdbc.db.ConnectionFactory;
import cn.jfast.framework.jdbc.db.DBProp;
import cn.jfast.framework.jdbc.info.Table;
import cn.jfast.framework.jdbc.orm.Executor;
import cn.jfast.framework.jdbc.orm.Record;
import cn.jfast.framework.jdbc.orm.RecordSet;
import cn.jfast.framework.jdbc.orm.SqlException;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.*;

public class NamedSql extends Executor {

	private String methodName;
	private ResultSet rs;
	private Table table = null;
	private static final String YES = "yes";
	private static final String EMPTY = " ";
	private static final String QUERY = "query";
	private static final String INTO = "Into";
	private static final String WHERE = "Where";
	private static final String GET = "get";
	private static final String AND = "And";
	private static final String OR = "Or";
	private static final String SELECT = "select";
	private static final String FIND = "find";
	private static final String REMOVE = "remove";
	private static final String UPDATE = "update";
	private static final String REFRESH = "refresh";
	private static final String DELETE = "delete";
	private static final String INSERT = "insert";
	private static final String ADD = "add";
	private static final String CREATE = "create";
	private static final String SAVE = "save";
	private static final String ALL = "All";
	private static final String VALUE = "Value";
	private static final String BY = "By";
	private static final String FROM = "From";
	private static final String SET = "Set";
	private static final String ASC = "Asc";
	private static final String DESC = "Desc";
	private List<String> availableColumns;
	private String tempColumn;
	private String mark;

	public NamedSql(Type[] paramTypes, String[] paramNames, Object[] args,
			String methodName, Type returnType, Method method)
			throws IllegalAccessException, SqlException,
			ClassNotFoundException, SQLException {
		Assert.notNull(method);
		Assert.notNull(methodName);
		if(DBProp.DB_NAME.toLowerCase().equals("mysql"))
			mark = "`";
		else if(DBProp.DB_NAME.toLowerCase().equals("oracle"))
			mark = "\"";
		this.method = method;
		this.paramNames = paramNames;
		this.paramTypes = paramTypes;
		this.args = args;
		this.methodName = methodName;
		this.returnType = returnType;
		conn = ConnectionFactory.getThreadLocalConnection();
		this.paramMaps = wrapParam();
	}

	@Override
	public Object execute() throws IllegalAccessException,
			NoSuchFieldException, InstantiationException, SQLException,
			SqlException {
		Object reObj = null;
		String sql = formatSql(parseSql());
		if (sql.startsWith("SELECT")) {
			ps = conn.prepareStatement(sql);
			fillPreparedStatement();
			rs = ps.executeQuery();
			ResultSetMetaData meta= rs.getMetaData();
			int column = meta.getColumnCount();
			String[] columns = new String[column];
			int[] columntypes = new int[column];
			for (int i = 0; i < column; i++) {
				columns[i] = meta.getColumnLabel(i + 1);
				columntypes[i] = meta.getColumnType(i + 1);
			}
			if (returnType instanceof ParameterizedType) { // 如果是带泛型类型的参数
				ParameterizedType parameterizedType = (ParameterizedType) returnType;
				Type basicType = parameterizedType.getRawType();
				Type[] paramTypes = parameterizedType.getActualTypeArguments();
				if (basicType == List.class) {
					Type fType = paramTypes[0];
					Class<?> fClass = (Class<?>) fType;
					List<Object> result = new ArrayList<Object>();
					while (rs.next()) {
						if (ClassUtils.isCommonTypeOrWrapper(fClass)) {
							if (column > 1)
								throw new SqlException(ErrorCode.MULTI_COLUMN);
							else
								result.add(rs.getObject(1));
						} else if(fClass == Record.class){
							Record rc = new Record();
							for (int i = 0; i < column; i++) {
								rc.put(columns[i], rs.getObject(columns[i]));
							}
							result.add(rc);
						} else {
							Object tempObj = fClass.newInstance();
							List<Field> fields = new ArrayList<Field>(
									Arrays.asList(fClass.getDeclaredFields()));
							for (int i = 0; i < column; i++) {
								Field field = null;
								FieldLoop: for (Field f : fields) {
									if (f.getName()
											.toLowerCase()
											.equals(StringUtils
													.dbColumn2ModelColumn(columns[i]))) {
										field = f;
										fields.remove(f);
										break FieldLoop;
									}
								}
								if (null != field) {
									field.setAccessible(true);
									field.set(
											tempObj,
											getFieldValue(field,
													rs.getObject(columns[i])));
								}
							}
							result.add(tempObj);
						}
					}
					reObj = result;
				} else if (basicType == Set.class) {
					Type fType = paramTypes[0];
					Class<?> fClass = (Class<?>) fType;
					Set<Object> result = new HashSet<Object>();
					while (rs.next()) {
						if (ClassUtils.isCommonTypeOrWrapper(fClass)) {
							if (column > 1)
								throw new SqlException(ErrorCode.MULTI_COLUMN);
							else
								result.add(rs.getObject(1));
						} else if (fClass == Record.class){
							Record rc = new Record();
							for (int i = 0; i < column; i++) {
								rc.put(columns[i], rs.getObject(columns[i]));
							}
							result.add(rc);
						}  else {
							Object tempObj = fClass.newInstance();
							List<Field> fields = new ArrayList<Field>(
									Arrays.asList(fClass.getDeclaredFields()));
							for (int i = 0; i < column; i++) {
								Field field = null;
								FieldLoop: for (Field f : fields) {
									if (f.getName()
											.toLowerCase()
											.equals(StringUtils
													.dbColumn2ModelColumn(columns[i]
															.toLowerCase()))) {
										field = f;
										fields.remove(f);
										break FieldLoop;
									}
								}
								if (null != field) {
									field.setAccessible(true);
									field.set(
											tempObj,
											getFieldValue(field,
													rs.getObject(columns[i])));
								}
							}
							result.add(tempObj);
						}
					}
					reObj = result;
				} else if (basicType == Map.class) {
					Type fType1 = paramTypes[0];
					if (fType1 != String.class)
						throw new SqlException(ErrorCode.ERROR_RETURN_TYPE);
					Map<String, Object> result = new LinkedHashMap<String, Object>();
					if (rs.next()) {
						for (int i = 0; i < column; i++)
							result.put(columns[i], rs
									.getObject(columns[i]));
						if (rs.next())
							throw new SqlException(ErrorCode.MULTI_ROW);
					}
					reObj = result;
				}
			} else {
				if (ClassUtils.isCommonTypeOrWrapper((Class<?>) returnType)) {
					if (column > 1)
						throw new SqlException(ErrorCode.MULTI_COLUMN);
					if (rs.next()) {
						reObj = rs.getObject(columns[0]);
						if (rs.next())
							throw new SqlException(ErrorCode.MULTI_ROW);
					}
				} else if (returnType == Record.class) {
					Record result = new Record();
					if (rs.next()) {
						for (int i = 0; i < column; i++)
							result.put(columns[i], rs
									.getObject(columns[i]));
						if (rs.next())
							throw new SqlException(ErrorCode.MULTI_ROW);
					}
					reObj = result;
				} else if (returnType == RecordSet.class) {
					RecordSet result = new RecordSet();
					while (rs.next()) {
						Record record = new Record();
						for (int i = 0; i < column; i++) {
							record.put(columns[i], rs
									.getObject(columns[i]));
						}
						result.addRecord(record);
					}
					reObj = result;
				} else if (returnType == List.class) {
					List<Object> result = new ArrayList<Object>();
					while (rs.next()) {
						Map<String, Object> tempMap = new LinkedHashMap<String, Object>();
						for (int i = 0; i < column; i++) {
							tempMap.put(columns[i], rs
									.getObject(columns[i]));
						}
						result.add(tempMap);
					}
					reObj = result;
				} else if (returnType == Set.class) {
					Set<Object> result = new HashSet<Object>();
					while (rs.next()) {
						Map<String, Object> tempMap = new LinkedHashMap<String, Object>();
						for (int i = 0; i < column; i++) {
							tempMap.put(columns[i], rs
									.getObject(columns[i]));
						}
						result.add(tempMap);
					}
					reObj = result;
				} else if (returnType == Map.class) {
					Map<String, Object> result = new LinkedHashMap<String, Object>();
					if (rs.next()) {
						for (int i = 0; i < column; i++)
							result.put(columns[i], rs
									.getObject(columns[i]));
						if (rs.next())
							throw new SqlException(ErrorCode.MULTI_ROW);
					}
					reObj = result;
				} else {
					Class<?> clazz = (Class<?>) returnType;
					List<Field> fields = new ArrayList<Field>(
							Arrays.asList(clazz.getDeclaredFields()));
					if (rs.next()) {
						Object result = clazz.newInstance();
						for (int i = 0; i < column; i++) {
							Field field = null;
							FieldLoop: for (Field f : fields) {
								if (f.getName()
										.toLowerCase()
										.equals(StringUtils
												.dbColumn2ModelColumn(columns[i]))) {
									field = f;
									fields.remove(f);
									break FieldLoop;
								}
							}
							if (null != field) {
								field.setAccessible(true);
								field.set(
										result,
										getFieldValue(field,
												rs.getObject(columns[i])));
							}
						}
						reObj = result;
						if (rs.next())
							throw new SqlException(ErrorCode.MULTI_ROW);
					}
				}
			}
		} else if (sql.startsWith("INSERT")) {
			ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			fillPreparedStatement();
			if (returnType == void.class)
				ps.executeUpdate();
			else if (returnType == Boolean.class || returnType == Boolean.TYPE)
				reObj = ps.execute();
			else if (returnType == Integer.class || returnType == Integer.TYPE) {
				DatabaseMetaData dmd = conn.getMetaData();
				ps.executeUpdate();
				if (dmd.supportsGetGeneratedKeys()) {
					rs = ps.getGeneratedKeys();
					if (rs.next()) {
						if (null != conn && !conn.isClosed() && conn.getAutoCommit() == true)
							conn.close();
						return rs.getInt(1);
					}
				} else {
					throw new SqlException(
							ErrorCode.NOT_SUPPORT_GET_GENERATEDKEY);
				}
			}
			else if (returnType == Long.class || returnType == Long.TYPE) {
				ps.executeUpdate();
				DatabaseMetaData dmd = conn.getMetaData();
				if (dmd.supportsGetGeneratedKeys()) {
					rs = ps.getGeneratedKeys();
					if (rs.next()) {
						if (null != conn && !conn.isClosed() && conn.getAutoCommit() == true)
							conn.close();
						return rs.getLong(1);
					}
				} else {
					throw new SqlException(
							ErrorCode.NOT_SUPPORT_GET_GENERATEDKEY);
				}
			}
		} else {
			ps = conn.prepareStatement(sql);
			fillPreparedStatement();
			if (returnType == void.class)
				ps.executeUpdate();
			else if (returnType == Boolean.class || returnType == Boolean.TYPE)
				reObj = ps.execute();
			else if (returnType == Integer.class || returnType == Integer.TYPE)
				reObj = ps.executeUpdate();
		}
		if (null != conn && !conn.isClosed() && conn.getAutoCommit() == true)
			conn.close();
		return reObj;
	}

	private String formatSql(String s) {
		return s.replaceAll("null", "").replaceFirst("AND", "")
				.replaceAll("\\s+", " ").replaceAll(" , ", ", ");
	}

	@Override
	public String getSql() throws SqlException {
		// 方法名验证
		if (methodName.endsWith(SELECT) || methodName.endsWith(QUERY)
				|| methodName.endsWith(GET) || methodName.endsWith(UPDATE)
				|| methodName.endsWith(REFRESH) || methodName.endsWith(INSERT)
				|| methodName.endsWith(CREATE) || methodName.endsWith(SAVE)
				|| methodName.endsWith(DELETE) || methodName.endsWith(REMOVE)
				|| methodName.endsWith(SET) || methodName.endsWith(FROM)
				|| methodName.endsWith(WHERE) || methodName.endsWith(BY)
				|| methodName.endsWith(AND) || methodName.endsWith(OR)
				|| methodName.endsWith(ALL) || methodName.endsWith(ASC)
				|| methodName.endsWith(DESC) || methodName.endsWith(INTO)
				|| methodName.endsWith(VALUE))
			throw new SqlException(ErrorCode.ERROR_METHOD_NAME);

		if (methodName.startsWith(SELECT) || methodName.startsWith(GET)
				|| methodName.startsWith(QUERY) || methodName.startsWith(FIND)) {
			return generateSelectSql();
		} else if (methodName.startsWith(UPDATE)) {
			return generateUpdateSql();
		} else if (methodName.startsWith(DELETE)
				|| methodName.startsWith(REMOVE)) {
			return generateDeleteSql();
		} else if (methodName.startsWith(INSERT) || methodName.startsWith(SAVE)
				|| methodName.startsWith(ADD)) {
			return generateInsertSql();
		} else {
			throw new SqlException(ErrorCode.ERROR_METHOD_NAME);
		}
	}

	private String generateSelectSql() throws SqlException {
		String[] selectColumns = null;
		String[] whereColumns = null;
		String[] orderColumns = null;
		// 获取表名
		if (StringUtils.contains(methodName, FROM)) {
			if (StringUtils.contains(methodName, BY))
				table = DBProp.getTable(StringUtils.substringBetween(
						methodName, FROM, BY));
			else if (StringUtils.contains(methodName, WHERE))
				table = DBProp.getTable(StringUtils.substringBetween(
						methodName, FROM, WHERE));
			else if (StringUtils.contains(methodName, ASC))
				table = DBProp.getTable(StringUtils.substringBetween(
						methodName, FROM, ASC));
			else if (StringUtils.contains(methodName, DESC))
				table = DBProp.getTable(StringUtils.substringBetween(
						methodName, FROM, DESC));
			else
				table = DBProp.getTable(StringUtils.substringBetween(methodName
						+ EMPTY, FROM, EMPTY));
		} else {
			if (StringUtils.contains(methodName, BY)) {
				if (methodName.startsWith(SELECT))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, SELECT, BY));
				else if (methodName.startsWith(GET))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, GET, BY));
				else if (methodName.startsWith(QUERY))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, QUERY, BY));
				else if (methodName.startsWith(FIND))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, FIND, BY));
			} else if (StringUtils.contains(methodName, WHERE)) {
				if (methodName.startsWith(SELECT))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, SELECT, WHERE));
				else if (methodName.startsWith(GET))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, GET, WHERE));
				else if (methodName.startsWith(FIND))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, FIND, WHERE));
			} else {
				if (StringUtils.contains(methodName, ASC)) {
					if (methodName.startsWith(SELECT))
						table = DBProp.getTable(StringUtils.substringBetween(
								methodName, SELECT, ASC));
					else if (methodName.startsWith(GET))
						table = DBProp.getTable(StringUtils.substringBetween(
								methodName, GET, ASC));
					else if (methodName.startsWith(QUERY))
						table = DBProp.getTable(StringUtils.substringBetween(
								methodName, QUERY, ASC));
					else if (methodName.startsWith(FIND))
						table = DBProp.getTable(StringUtils.substringBetween(
								methodName, FIND, ASC));
				} else if (StringUtils.contains(methodName, DESC)) {
					if (methodName.startsWith(SELECT))
						table = DBProp.getTable(StringUtils.substringBetween(
								methodName, SELECT, DESC));
					else if (methodName.startsWith(GET))
						table = DBProp.getTable(StringUtils.substringBetween(
								methodName, GET, DESC));
					else if (methodName.startsWith(QUERY))
						table = DBProp.getTable(StringUtils.substringBetween(
								methodName, QUERY, DESC));
					else if (methodName.startsWith(FIND))
						table = DBProp.getTable(StringUtils.substringBetween(
								methodName, FIND, DESC));
				} else {
					if (methodName.startsWith(SELECT))
						table = DBProp.getTable(StringUtils.substringBetween(
								methodName + EMPTY, SELECT, EMPTY));
					else if (methodName.startsWith(GET))
						table = DBProp.getTable(StringUtils.substringBetween(
								methodName + EMPTY, GET, EMPTY));
					else if (methodName.startsWith(QUERY))
						table = DBProp.getTable(StringUtils.substringBetween(
								methodName + EMPTY, QUERY, EMPTY));
					else if (methodName.startsWith(FIND))
						table = DBProp.getTable(StringUtils.substringBetween(
								methodName + EMPTY, FIND, EMPTY));
				}
			}
		}
		if (null == table)
			throw new SqlException(ErrorCode.NO_SUCH_TABLE);
		// 获取SELECT域
		if (StringUtils.contains(methodName, FROM)) {
			if (methodName.startsWith(SELECT))
				selectColumns = StringUtils.splitByUpperCase(StringUtils
						.substringBetween(methodName, SELECT, FROM)); // 查询字段
			else if (methodName.startsWith(QUERY))
				selectColumns = StringUtils.splitByUpperCase(StringUtils
						.substringBetween(methodName, QUERY, FROM)); // 查询字段
			else if (methodName.startsWith(GET))
				selectColumns = StringUtils.splitByUpperCase(StringUtils
						.substringBetween(methodName, GET, FROM)); // 查询字段
		}
		// 获取WHERE域
		if (StringUtils.contains(methodName, BY)) {
			if (StringUtils.contains(methodName, ASC))
				whereColumns = StringUtils.splitByUpperCase(StringUtils
						.substringBetween(methodName, BY, ASC)); // Where条件
			else if (StringUtils.contains(methodName, DESC))
				whereColumns = StringUtils.splitByUpperCase(StringUtils
						.substringBetween(methodName, BY, DESC)); // Where条件
			else
				whereColumns = StringUtils.splitByUpperCase(StringUtils
						.substringBetween(methodName + " ", BY, " ")); // Where条件
		} else if (StringUtils.contains(methodName, WHERE)) {
			if (StringUtils.contains(methodName, ASC))
				whereColumns = StringUtils.splitByUpperCase(StringUtils
						.substringBetween(methodName, WHERE, ASC)); // Where条件
			else if (StringUtils.contains(methodName, DESC))
				whereColumns = StringUtils.splitByUpperCase(StringUtils
						.substringBetween(methodName, WHERE, DESC)); // Where条件
			else
				whereColumns = StringUtils.splitByUpperCase(StringUtils
						.substringBetween(methodName + " ", WHERE, " ")); // Where条件
		} else {
			if (!CollectionUtils.isEmpty(paramMaps))
				whereColumns = paramMaps.keySet().toArray(new String[] {});
		}

		// 获取ORDER域
		if (StringUtils.contains(methodName, ASC))
			orderColumns = StringUtils.splitByUpperCase(StringUtils
					.substringBetween(methodName + " ", ASC, " ")); // Where条件
		else if (StringUtils.contains(methodName, DESC))
			orderColumns = StringUtils.splitByUpperCase(StringUtils
					.substringBetween(methodName + " ", DESC, " ")); // Where条件

		// 字段智能分析
		selectColumns = columnAi(table, selectColumns, 1); // 过滤数据表中不存在字段
		whereColumns = columnAi(table, whereColumns, 2); // 过滤数据表和参数中不存在字段
		orderColumns = columnAi(table, orderColumns, 1); // 过滤数据表中不存在字段

		// 拼装SQL
		StringBuffer sbf = new StringBuffer("SELECT ");
		if (null == selectColumns) {
			sbf.append("* FROM ");
		} else {
			for (String column : selectColumns) {
				if (!column.equals(AND) && !column.equals(OR))
					sbf.append(mark+column.toLowerCase()+mark + " , ");
			}
			sbf.deleteCharAt(sbf.lastIndexOf(",")).append(" FROM ");
		}
		sbf.append(mark+table.getDbName()+mark);
		if (null != whereColumns) {
			sbf.append(" WHERE ");
			for (String column : whereColumns) {
				if (!column.equals(AND) && !column.equals(OR))
					if (sbf.lastIndexOf("OR") != (sbf.length() - 3))
						sbf.append(" AND " + column.toLowerCase() + " = :"
								+ StringUtils.dbColumn2ModelColumn(column));
					else
						sbf.append(column.toLowerCase() + " = :"
								+ StringUtils.dbColumn2ModelColumn(column));
				else if (column.equals(OR))
					sbf.append(" OR ");
			}
		}
		if (null != orderColumns) {
			sbf.append(" ORDER BY ");
			for (String column : orderColumns) {
				if (!column.equals(AND) && !column.equals(OR))
					sbf.append(" " + column.toLowerCase() + " , ");
				else if (column.equals(OR))
					throw new SqlException(ErrorCode.ERROR_METHOD_NAME);
			}
			sbf.deleteCharAt(sbf.lastIndexOf(","));
		}
		return sbf.toString();
	}

	private String generateUpdateSql() throws SqlException {
		String[] updateColumns = null;
		String[] whereColumns = null;
		// 获取表名
		if (StringUtils.contains(methodName, FROM)) {
			if (StringUtils.contains(methodName, SET))
				table = DBProp.getTable(StringUtils.substringBetween(
						methodName, FROM, SET));
			else if (StringUtils.contains(methodName, BY))
				table = DBProp.getTable(StringUtils.substringBetween(
						methodName, FROM, BY));
			else if (StringUtils.contains(methodName, WHERE))
				table = DBProp.getTable(StringUtils.substringBetween(
						methodName, FROM, WHERE));
			else
				table = DBProp.getTable(StringUtils.substringBetween(methodName
						+ EMPTY, FROM, EMPTY));
		} else {
			if (StringUtils.contains(methodName, SET)) {
				if (methodName.startsWith(UPDATE))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, UPDATE, SET));
				else if (methodName.startsWith(REFRESH))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, REFRESH, SET));
			} else if (StringUtils.contains(methodName, WHERE)) {
				if (methodName.startsWith(UPDATE))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, UPDATE, WHERE));
				else if (methodName.startsWith(REFRESH))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, REFRESH, WHERE));
			} else if (StringUtils.contains(methodName, BY)) {
				if (methodName.startsWith(UPDATE))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, UPDATE, BY));
				else if (methodName.startsWith(REFRESH))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, REFRESH, BY));
			} else {
				if (methodName.startsWith(UPDATE))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName + EMPTY, UPDATE, EMPTY));
				else if (methodName.startsWith(REFRESH))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName + EMPTY, REFRESH, EMPTY));
			}
		}
		if (null == table)
			throw new SqlException(ErrorCode.NO_SUCH_TABLE);
		// 获取UPDATE域
		if (StringUtils.contains(methodName, SET)) {
			if (StringUtils.contains(methodName, BY))
				updateColumns = StringUtils.splitByUpperCase(StringUtils
						.substringBetween(methodName, SET, BY)); // 更新字段
			else if (StringUtils.contains(methodName, WHERE))
				updateColumns = StringUtils.splitByUpperCase(StringUtils
						.substringBetween(methodName, SET, WHERE)); // 更新字段
			else
				updateColumns = StringUtils.splitByUpperCase(StringUtils
						.substringBetween(methodName + EMPTY, SET, EMPTY)); // 更新字段
		} else {
			List<String> updateList = new ArrayList<String>(table.getColumn()
					.keySet());
			updateList.removeAll(Arrays.asList(table.getPrimaryKey()));
			updateColumns = updateList.toArray(new String[] {});
		}
		// 获取WHERE域
		if (StringUtils.contains(methodName, BY)) {
			whereColumns = StringUtils.splitByUpperCase(StringUtils
					.substringBetween(methodName + EMPTY, BY, EMPTY)); // Where条件
		} else if (StringUtils.contains(methodName, WHERE)) {
			whereColumns = StringUtils.splitByUpperCase(StringUtils
					.substringBetween(methodName + EMPTY, WHERE, EMPTY)); // Where条件
		} else {
			whereColumns = table.getPrimaryKey();
		}
		// 字段智能分析
		updateColumns = columnAi(table, updateColumns, 2); // 过滤数据表和参数中不存在字段
		whereColumns = columnAi(table, whereColumns, 2); // 过滤数据表和参数中不存在字段
		// 拼装SQL
		StringBuffer sbf = new StringBuffer("UPDATE " + mark+table.getDbName()+mark
				+ " SET ");
		if (ObjectUtils.isEmpty(updateColumns))
			throw new SqlException(ErrorCode.NO_FIELD_NEED_TO_UPDATE);
		for (String column : updateColumns) {
			if (!column.equals(AND) && !column.equals(OR))
				sbf.append(column.toLowerCase() + " = :"
						+ StringUtils.dbColumn2ModelColumn(column) + " , ");
		}
		sbf.deleteCharAt(sbf.lastIndexOf(","));
		if (ObjectUtils.isEmpty(whereColumns))
			throw new SqlException(ErrorCode.NO_KEY_FIELD);
		sbf.append(" WHERE ");
		for (String column : whereColumns) {
			if (!column.equals(AND) && !column.equals(OR))
				if (sbf.lastIndexOf("OR") != (sbf.length() - 3))
					sbf.append(" AND " + column.toLowerCase() + " = :"
							+ StringUtils.dbColumn2ModelColumn(column));
				else
					sbf.append(column.toLowerCase() + " = :"
							+ StringUtils.dbColumn2ModelColumn(column));
			else if (column.equals(OR))
				sbf.append(" OR ");
		}
		return sbf.toString();
	}

	private String generateInsertSql() throws SqlException {
		String[] insertColumns;
		List<String> columns;
		List<Column> insertList;
		// 获取表名
		if (StringUtils.contains(methodName, INTO)) {
			if (StringUtils.contains(methodName, VALUE))
				table = DBProp.getTable(StringUtils.substringBetween(
						methodName, INTO, VALUE));
			else
				table = DBProp.getTable(StringUtils.substringBetween(methodName
						+ EMPTY, INTO, EMPTY));
		} else {
			if (StringUtils.contains(methodName, INSERT)) {
				if (methodName.startsWith(VALUE))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, INSERT, VALUE));
				else
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName + EMPTY, INSERT, EMPTY));
			} else if (StringUtils.contains(methodName, SAVE)) {
				if (methodName.startsWith(VALUE))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, SAVE, VALUE));
				else
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName + EMPTY, SAVE, EMPTY));
			} else if (StringUtils.contains(methodName, CREATE)) {
				if (methodName.startsWith(VALUE))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, CREATE, VALUE));
				else
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName + EMPTY, CREATE, EMPTY));
			} else if (StringUtils.contains(methodName, ADD)) {
				if (methodName.startsWith(VALUE))
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName, ADD, VALUE));
				else
					table = DBProp.getTable(StringUtils.substringBetween(
							methodName + EMPTY, ADD, EMPTY));
			}
		}
		if (null == table)
			throw new SqlException(ErrorCode.NO_SUCH_TABLE);
		// 获取INSERT域
		if (StringUtils.contains(methodName, VALUE)) {
			insertColumns = StringUtils.splitByUpperCase(StringUtils
					.substringBetween(methodName + EMPTY, VALUE, EMPTY)); // 插入字段
		} else {
			insertList = new ArrayList<Column>(table.getColumn().values());
			columns = new ArrayList<String>();
			for (Column column : insertList) {
				if (!column.getAutoIncrement().toLowerCase().equals(YES))
					columns.add(column.getName());
			}
			insertColumns = columns.toArray(new String[] {});
		}
		// 过滤自增字段
		columns = new ArrayList<String>();
		for (int i = 0; i < insertColumns.length; i++) {
			Column col = table.getColumn(StringUtils
					.dbColumn2ModelColumn(insertColumns[i]));
			if (null != col)
				if (!col.getAutoIncrement().toLowerCase().equals(YES)) {
					columns.add(col.getName());
				}
		}
		// 字段智能分析
		insertColumns = columnAi(table, columns.toArray(new String[] {}), 2); // 过滤数据表和参数中不存在字段
		// 拼装SQL
		StringBuffer sbf = new StringBuffer("INSERT INTO " + mark+table.getDbName()+mark
				+ " ( ");
		if (ObjectUtils.isEmpty(insertColumns))
			throw new SqlException(ErrorCode.NO_FIELD_NEED_TO_INSERT);
		for (String column : insertColumns) {
			if (!column.equals(AND) && !column.equals(OR))
				sbf.append(column.toLowerCase() + " , ");
		}
		sbf.deleteCharAt(sbf.lastIndexOf(",")).append(") VALUES (");
		for (String column : insertColumns) {
			if (!column.equals(AND) && !column.equals(OR))
				sbf.append(" :" + StringUtils.dbColumn2ModelColumn(column)
						+ " , ");
		}
		sbf.deleteCharAt(sbf.lastIndexOf(",")).append(")");
		return sbf.toString();
	}

	private String generateDeleteSql() throws SqlException {
		String[] whereColumns = null;
		// 获取表名
		if (StringUtils.contains(methodName, FROM)) {
			if (StringUtils.contains(methodName, BY))
				table = DBProp.getTable(StringUtils.substringBetween(
						methodName, FROM, BY));
			else if (StringUtils.contains(methodName, WHERE))
				table = DBProp.getTable(StringUtils.substringBetween(
						methodName, FROM, WHERE));
			else
				table = DBProp.getTable(StringUtils.substringBetween(methodName
						+ EMPTY, FROM, EMPTY));
		} else {
			if (StringUtils.contains(methodName, BY))
				table = DBProp.getTable(StringUtils.substringBetween(
						methodName, DELETE, BY));
			else if (StringUtils.contains(methodName, WHERE))
				table = DBProp.getTable(StringUtils.substringBetween(
						methodName, DELETE, WHERE));
			else
				table = DBProp.getTable(StringUtils.substringBetween(methodName
						+ EMPTY, DELETE, EMPTY));
		}
		if (null == table)
			throw new SqlException(ErrorCode.NO_SUCH_TABLE);
		// 获取DELETE条件域
		if (StringUtils.contains(methodName, BY)) {
			whereColumns = StringUtils.splitByUpperCase(StringUtils
					.substringBetween(methodName + EMPTY, BY, EMPTY)); // 条件字段
		} else if (StringUtils.contains(methodName, WHERE)) {
			whereColumns = StringUtils.splitByUpperCase(StringUtils
					.substringBetween(methodName + EMPTY, WHERE, EMPTY)); // 条件字段
		} else {
			if (!CollectionUtils.isEmpty(paramMaps))
				whereColumns = paramMaps.keySet().toArray(new String[] {});
		}
		// 字段智能分析
		whereColumns = columnAi(table, whereColumns, 2); // 过滤数据表和参数中不存在字段
		// 拼装SQL
		StringBuffer sbf = new StringBuffer("DELETE FROM " + mark+table.getDbName()+mark);
		if (ObjectUtils.isEmpty(whereColumns))
			throw new SqlException(ErrorCode.NO_FIELD_NEED_TO_DELETE);
		sbf.append(" WHERE ");
		for (String column : whereColumns) {
			if (!column.equals(AND) && !column.equals(OR))
				if (sbf.lastIndexOf("OR") != (sbf.length() - 3))
					sbf.append(" AND " + column.toLowerCase() + " = :"
							+ StringUtils.dbColumn2ModelColumn(column));
				else
					sbf.append(column.toLowerCase() + " = :"
							+ StringUtils.dbColumn2ModelColumn(column));
			else if (column.equals(OR))
				sbf.append(" OR ");
		}
		return sbf.toString();
	}

	/**
	 * 过滤字段列表
	 * 
	 * @param table
	 *            表信息
	 * @param columns
	 *            参数列
	 * @param type
	 *            1 过滤数据表不存在字段 2 过滤数据表和参数中不存在字段
	 * @return
	 * @throws SqlException
	 */
	private String[] columnAi(Table table, String[] columns, int type)
			throws SqlException {
		availableColumns = new ArrayList<String>();
		tempColumn = "";
		if (ObjectUtils.isEmpty(columns))
			return null;
		for (int i = 0; i < columns.length; i++) {
			if (ObjectUtils.isNotNull(table.getColumn(StringUtils
					.dbColumn2ModelColumn(columns[i])))) {
				if (type == 2) { // 参数集合验证
					if (CollectionUtils.isEmpty(paramMaps)
							|| !paramMaps.containsKey(StringUtils.dbColumn2ModelColumn(columns[i])) 
							|| null == paramMaps.get(StringUtils.dbColumn2ModelColumn(columns[i]))) {
						continue;
					}
				}
				availableColumns
						.add(table.getColumn(
								StringUtils.dbColumn2ModelColumn(columns[i]))
								.getName());
				tempColumn = "";
			} else if (columns[i].equals(AND) || columns[i].equals(OR)) {
				tempColumn = "";
			} else if (columns[i].equals(ALL)) {
				return null;
			} else if (ObjectUtils.isNotNull(table.getColumn(StringUtils
					.dbColumn2ModelColumn((tempColumn + columns[i]))))) {
				if (type == 2) { // 参数集合验证
					if (CollectionUtils.isEmpty(paramMaps)
							|| !paramMaps.containsKey(StringUtils.dbColumn2ModelColumn(tempColumn+columns[i])) 
							|| null == paramMaps.get(StringUtils.dbColumn2ModelColumn(tempColumn+columns[i]))) {
						continue;
					}
				}
				availableColumns.add(table.getColumn(
										StringUtils.dbColumn2ModelColumn((tempColumn + columns[i]))).getName());
				tempColumn = "";
			} else {
				tempColumn = columns[i];
			}
		}
		if (availableColumns.size() == 0
				|| availableColumns.get(availableColumns.size() - 1)
						.equals(AND)
				|| availableColumns.get(availableColumns.size() - 1).equals(OR)
				|| availableColumns.get(0).equals(AND)
				|| availableColumns.get(0).equals(OR))
			throw new SqlException(ErrorCode.ERROR_METHOD_NAME);
		return availableColumns.toArray(new String[] {});
	}
}
